package com.kehutong.house.admin;

import java.io.File;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

import com.kehutong.admin.entity.Corp;
import com.kehutong.common.permission.DataPermissionQuery;
import com.kehutong.common.permission.PermissionNameDto;
import com.kehutong.house.entity.House;
import com.kehutong.house.service.BasicService;
import com.kehutong.house.service.BuildService;
import com.kehutong.house.service.CommunityService;
import com.kehutong.house.service.HouseService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.coraframework.authz.HasPermission;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.logger.Logger;
import org.coraframework.logger.LoggerFactory;
import org.coraframework.mvc.MvcAction;
import org.coraframework.mvc.http.Bean;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.connection.ThreadLocalCache;
import org.coraframework.orm.jdbc.execute.entity.FindArray;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.EnumUtil;
import org.coraframework.util.Objects;

import com.kehutong.common.DoveClient;
import com.kehutong.common.ExcelUtil;
import com.kehutong.common.urlconfig.FileUrlConfig;
import com.kehutong.common.util.Token;
import com.kehutong.house.entity.Building;
import com.kehutong.house.entity.Community;
import com.kehutong.house.enums.BuildType;
import com.kehutong.house.enums.PropertyType;

@MvcAction
@ReqMapping("/admin/basic/community")
public class CommunityController {
	
	@Inject
	private JdbcSession sqlSession;
    @Inject
    private DoveClient doveClient;
    @Inject
    private FileUrlConfig fileUrlConfig;
    @Inject
    private BuildService buildService;
    @Inject
    private HouseService houseService;
    @Inject
    private BasicService basicService;
    @Inject
    private CommunityService communityService;


    private final static String[] EXCEL_IMPORT_HEADER = {"项目名称","别名","所在地区","详细地址","楼栋数","总户数","停车位","商铺数"
            ,"开发商","建筑类别","装修状况","占地面积(㎡)","建筑面积(㎡)","容积率","绿化率","产权年限","所属部门","物业管家"};

    static final Logger logger = LoggerFactory.getLogger(CommunityController.class);

    private final static String businessType = "1100";
	
    @HasPermission("basic:community:view")
    @ReqMapping("/listById")
    public Object listById(JSONArray jsonArray) {
        FindArray<Community> find = sqlSession.findArray(Community.class)
				.in("id", jsonArray);
        PermissionNameDto dto = houseService.getCommunityPermissionNameDto();
        DataPermissionQuery.setAllDataSql(find, dto, null);

        return find.exe();
    }

    @HasPermission("basic:community:view")
    @ReqMapping("/listAll")
    public Object listAll(JSONObject jsonObject) throws Exception {
        FindArray<Community> find = sqlSession.findArray(Community.class)
                .like(jsonObject, "name")
                .eq("deleted", false);
        PermissionNameDto dto = houseService.getCommunityPermissionNameDto();
        DataPermissionQuery.setAllDataSql(find, dto, null);
        return find.exe();
    }

    @HasPermission("basic:community:view")
    @ReqMapping("/list")
    public Object list(JSONObject jsonObject) {
    	FindPage<Community> findPage = sqlSession.findPage(Community.class);

    	JSONArray employee_ids = jsonObject.getJSONArray("employee_ids");
    	if(Objects.nonEmpty(employee_ids)){
            findPage.markBegin();
            for (Object item: employee_ids) {
                findPage.like("employee_ids",String.valueOf(item)).or();
            }
            findPage.markEnd().and();
        }

        findPage.eq(jsonObject, "area_id")
                .in(jsonObject, "parent_id")
    			.like(jsonObject, "name", "viewName","area_names","parent_ids", "area_ids")
				.eq("deleted", false)
                .order("createTime", "desc")
                .page(jsonObject);

        try{
            PermissionNameDto dto = houseService.getCommunityPermissionNameDto();
            houseService.setDataSql(findPage, dto, jsonObject, null);
            Page<Community> page = findPage.exe(false);

            Set<String> departmentIds = new HashSet<>();
            Set<String> employeeIds = new HashSet<>();
            page.getList().forEach(item -> {
                if(Objects.nonEmpty(item.getParent_id())){
                    departmentIds.add(item.getParent_id());
                }
                if(Objects.nonEmpty(item.getEmployee_ids())){
                    employeeIds.addAll(item.getEmployee_ids());
                }
            });

            Map<String,JSONObject> departmentMap = communityService.getDepartmentMap(new ArrayList<>(departmentIds));
            Map<String,JSONObject> employeeMap = communityService.getEmployeeMap(new ArrayList<>(employeeIds));

            page.getList().forEach(item -> {
                communityService.setParentName(departmentMap, employeeMap, item);
            });

            return page;
        }catch (Exception e){
            logger.debug("page=========", e);
        }
        return new Page<>();
    }

    @HasPermission("basic:community:view")
    @ReqMapping("/tree")
    public Object tree(JSONObject jsonObject) throws Exception {
        Community item = sqlSession.findOne(Community.class)
                .eq(jsonObject, "id")
                .eq("deleted", false)
                .exe();

        JSONObject json = new JSONObject();
        json.put("id", item.getId());
        json.put("name", item.getName());
        json.put("checkd", false);

        // 获取楼栋信息
        List<Building> builds = buildService.list(item.getId());

        List<JSONObject> buildJsons = new ArrayList<>();
        for (Building build:  builds) {
            JSONObject buildJson = new JSONObject();
            buildJson.put("id", build.getId());
            buildJson.put("name", Objects.nonEmpty(build.getUnitName()) ? build.getName() + "-" + build.getUnitName() : build.getName());
            buildJson.put("checkd", false);

            List<House> houses = houseService.list(item.getId(), build.getId());

            List<JSONObject> houseJsons = new ArrayList<>();
            for (House house:  houses) {
                JSONObject houseJson = new JSONObject();
                houseJson.put("id", house.getId());
                houseJson.put("name", house.getFloorName() + house.getName());
                houseJson.put("desc", getDesc(build,house));
                boolean type = jsonObject.getBoolean("type");
                if(type){
                    JSONArray instruments = this.getInstruments(item.getId(), house.getId(), jsonObject.getString("instrumentTypeId"));
                    List<JSONObject> insJsons = new ArrayList<>();
                    instruments.forEach(obj ->{
                        JSONObject instrument = (JSONObject) obj;
                        JSONObject ins = new JSONObject();
                        ins.put("id", instrument.getString("id"));
                        ins.put("name", instrument.getString("instrumentNo"));
                        ins.put("checkd", true);
                        insJsons.add(ins);
                    });
                    houseJson.put("checkd", false);
                    houseJson.put("childList", insJsons);
                }else{
                    houseJson.put("checkd", true);
                }
                houseJsons.add(houseJson);
            }
            buildJson.put("childList", houseJsons);
            buildJsons.add(buildJson);
        }
        json.put("childList", buildJsons);

        return json;
    }

    public String getDesc(Building build,House house){
        StringBuilder builder = new StringBuilder();
        if(Objects.nonEmpty(build.getUnitName())){
            builder.append(build.getName())
                    .append("-")
                    .append(build.getUnitName());
        }else{
            builder.append(build.getName());
        }

        builder.append("-")
                .append(house.getFloorName())
                .append(house.getName());
        return builder.toString();
    }

    public JSONArray getInstruments(String communityNo, String houseId, String instrumentTypeId) throws Exception {
        JSONObject result = doveClient.post("/payfees/charge/user/instrument/service/listAll", http->{
            JSONObject body = new JSONObject();
            body.put("communityNo", communityNo);
            body.put("houseId", houseId);
            body.put("instrumentTypeId", instrumentTypeId);
            http.setBody(body.toString());
        });

        JSONArray data = result.getJSONArray("data");

        if (Objects.isNull(data)) {
            return null;
        }

        return data;
    }

    @HasPermission("basic:community:view")
    @ReqMapping("/get")
    public Object get(Community item) throws Exception {
    	return communityService.get(item);
    }

    @Bean(newInstance=true, copy=true)
    @HasPermission("basic:community:edit")
    @ReqMapping("/save")
    public Object save(Token token, Community community) throws Exception {
    	return communityService.save(token, community);
    }

    @Bean(copy=true, isIgnoreNull = false)
    @HasPermission("basic:community:edit")
    @ReqMapping("/update")
    public Object update(Token token, Community community, JSONObject jsonObject) throws Exception {
        return communityService.update(token, community, jsonObject);
    }

    @HasPermission("basic:community:edit")
    @ReqMapping("/delete")
	public Object delete(Community item) {
        return communityService.delete(item);
	}

    @HasPermission("basic:community:view")
    @ReqMapping("/export")
    public Object export(JSONObject jsonObject) throws Exception {
        File tmpFile = null;
        Boolean isModel = jsonObject.getBoolean("isModel");

        if(isModel){
            Workbook wb = generateWorkBook();

            tmpFile = ExcelUtil.writeXlsFile(wb, "小区导入模板", ".xlsx");
        } else {
            FindArray<Community> find = sqlSession.findArray(Community.class);
            find.eq("deleted", false);
            PermissionNameDto dto = houseService.getCommunityPermissionNameDto();
            houseService.setDataSql(find, dto, jsonObject, null);
            List<Community> list = find.exe(false);
            Workbook wb = generateWorkBook(list);

            tmpFile = ExcelUtil.writeXlsFile(wb, "小区信息", ".xlsx");
        }
        return Result.download(tmpFile);
    }

    @ReqMapping("/checkFile")
    public Object checkFile(JSONObject jsonObject) throws Exception {
        InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        // 3行表头 开始读
        Row firstRow = sheet.getRow(sheet.getFirstRowNum() + 2);
        List<String> errMsgs = new ArrayList<>();

        for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
            firstRow.getCell(i).setCellType(CellType.STRING);
            String s = firstRow.getCell(i).getStringCellValue();
            if (i < EXCEL_IMPORT_HEADER.length) {
                if (!EXCEL_IMPORT_HEADER[i].equals(s)) {
                    errMsgs.add("请确认【" + EXCEL_IMPORT_HEADER[i] + "】为第" + (i + 1) + "列");
                }
            }
        }

        JSONObject errorJson = new JSONObject();
        // 表头校验结果
        if (!errMsgs.isEmpty()) {
            errorJson.put("normalCount", 0);
            errorJson.put("errCount", 0);
            errorJson.put("successCount", 0);
            errorJson.put("errMsgs", errMsgs);
            return Result.error(0, "数据解析异常", errorJson);
        }

        Map<Integer,Integer> errCount = new HashMap<>();
        Integer size = 0;

        Map<String,JSONObject> departmentMap = getDepartmentMapByName();
        Map<String,List<JSONObject>> departmentsMap = getDepartmentMapByPid();

        // 第4行开始遍历获取数据
        for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
            Row row = sheet.getRow(rIndex);
            if(Objects.isNull(row)){
                continue;
            }

            List<String> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (i < EXCEL_IMPORT_HEADER.length && Objects.nonNull(cell)) {
                    cell.setCellType(CellType.STRING);
                    String value = cell.getStringCellValue();
                    if(Objects.nonEmpty(value)){
                        cellValues.add(value);
                    }
                }
            }
            if(Objects.isEmpty(cellValues)){
                continue;
            }

            // 校验数据
            checkExcelData(row, (rIndex + 1), errMsgs, errCount, departmentMap, departmentsMap);

            size++;
        }

        errorJson.put("normalCount", size);
        if(size > 3000){
            errMsgs = new ArrayList<>();
            errMsgs.add("最多支持导入3000条数据");
            errorJson.put("errCount", 0);
            errorJson.put("successCount", 0);
            errorJson.put("errMsgs", errMsgs);
            return Result.error(0, "数据解析异常", errorJson);
        }

        // 数据读取结果
        if (!errMsgs.isEmpty()) {

            int errSum = 0;
            for (int val: errCount.keySet()) {
                errSum++;
            }

            errorJson.put("errMsgs", errMsgs);
            errorJson.put("errCount", errSum);
            errorJson.put("successCount", size - errSum);

            return Result.error(0, "数据解析异常", errorJson);
        }

        errorJson.put("errMsgs", errMsgs);
        errorJson.put("errCount", 0);
        errorJson.put("successCount", size);
        return Result.success("成功", errorJson);
    }

    @ReqMapping("/readFile")
    public Object readFile(Token token, JSONObject jsonObject) throws Exception {
        InputStream is = fileUrlConfig.download(jsonObject.getString("fileId"));
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        Map<String,JSONObject> departmentMap = getDepartmentMapByName();
        Map<String,List<JSONObject>> departmentsMap = getDepartmentMapByPid();

        List<JSONObject> jsons = new ArrayList<>();
        // 第4行开始遍历获取数据
        for (int rIndex = sheet.getFirstRowNum() + 3; rIndex <= sheet.getLastRowNum(); rIndex++) {
            Row row = sheet.getRow(rIndex);
            if(Objects.isNull(row)){
                continue;
            }

            List<String> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (i < EXCEL_IMPORT_HEADER.length && Objects.nonNull(cell)) {
                    cell.setCellType(CellType.STRING);
                    String value = cell.getStringCellValue();
                    if(Objects.nonEmpty(value)){
                        cellValues.add(value);
                    }
                }
            }
            if(Objects.isEmpty(cellValues)){
                continue;
            }

            JSONObject json = new JSONObject();
            // 校验数据
            checkExcelData(row, departmentMap, departmentsMap, json);

            // 写入房屋信息
            json.put("name", ExcelUtil.getCellValue(row.getCell(0)));
            json.put("viewName", ExcelUtil.getCellValue(row.getCell(1)));
            json.put("area_names", ExcelUtil.getCellValue(row.getCell(2)));
            json.put("address", ExcelUtil.getCellValue(row.getCell(3)));
            json.put("developer", ExcelUtil.getCellValue(row.getCell(8)));
            json.put("decorationDesc", ExcelUtil.getCellValue(row.getCell(10)));
            json.put("propertyYear", ExcelUtil.getCellValue(row.getCell(15)));

            jsons.add(json);
        }

        List<String> childDepartments = getChildDepartments(token.getDepartments());

        for (JSONObject json: jsons) {
            Community community = sqlSession.findOne(Community.class)
                    .eq(json, "name")
                    .eq("deleted", false)
                    .exe();

            if(Objects.nonNull(community)){
                setExcelData(community, json);
                // 增加部门转换的归属记录
                saveAttributeRecord(token, community, false);

                sqlSession.updateById(community);
                // 增加操作记录
                basicService.saveOperateRecord(token, community.getId(), businessType, "2", "上传文档修改");
            }else{
                community = Pool.newInstance(Community.class);
                setExcelData(community, json);
                community.setCreate_by(token.getUsername());
                community.setCreateUserId(token.getUuid());
                community.setCreateDepartment(token.getDepartments());
                community.setCreateChildDepartment(childDepartments);

                // 增加部门转换的归属记录
                saveAttributeRecord(token, community, true);

                sqlSession.insert(community);
                // 增加操作记录
                basicService.saveOperateRecord(token, community.getId(), businessType, "1", "上传文档新增");
            }
        }
        return Result.success();
    }

    @HasPermission("basic:community:view")
    @ReqMapping("/getCommunityTransfer")
    public Object getCommunityTransfer (JSONObject jsonObject) throws Exception {
        return communityService.getCommunityTransfer(jsonObject);
    }

    @HasPermission("basic:community:edit")
    @ReqMapping("/deleteAll")
    public Object deleteAll(JSONObject jsonObject){
        List<Community> list = sqlSession.findArray(Community.class)
                .in("id", jsonObject.getJSONArray("ids"))
                .eq("deleted", false)
                .exe();

        List<String> errMsgs = new ArrayList<>();
        list.forEach(item ->{
            List<Building> builds = sqlSession.findArray(Building.class)
                    .eq("communityNo", item.getId())
                    .eq("deleted", false)
                    .exe();

            if(Objects.nonEmpty(builds)){
                errMsgs.add("[" + item.getName() + "]需先删除该小区下的楼栋，再删除该小区！");
            }else{
                item.setDeleted(true);
            }
        });

        if (!errMsgs.isEmpty()) {
            return Result.error(0, "删除失败", errMsgs);
        }

        sqlSession.updateById(list);

        return Result.success();
    }

    /**
     * 批量设置 部门/管家
     * @param jsonObject
     * @return
     */
    @ReqMapping("/setting")
    public Object setting(JSONObject jsonObject){
        List<Community> list = sqlSession.findArray(Community.class)
                .in("id", jsonObject.getJSONArray("ids"))
                .eq("deleted", false)
                .exe();

        JSONArray parent_ids = jsonObject.getJSONArray("parent_ids");
        if(Objects.nonNull(parent_ids)){
            String parent_id = jsonObject.getString("parent_id");
            List<String> parents = JSONObject.parseArray(parent_ids.toString(),String.class);

            list.forEach(item -> {
                item.setParent_id(parent_id);
                item.setParent_ids(parents);
            });
        }else{
            JSONArray employee_ids = jsonObject.getJSONArray("employee_ids");
            List<String> employees = JSONObject.parseArray(employee_ids.toString(),String.class);
            list.forEach(item -> {
                item.setEmployee_ids(employees);
            });
        }

        sqlSession.updateById(list);

        return Result.success();
    }

    @ReqMapping("/setStatus")
    public Object setStatus(Community entity) {
        entity.setEnable(!entity.isEnable());
        sqlSession.updateById(entity);

        return Result.success();
    }

    @ReqMapping("/enums")
    public Object getEnums() {
        Map<String, Object> map = new HashMap<>();
        map.put("buildTypes", Arrays.asList(BuildType.values()));
        map.put("propertyTypes", Arrays.asList(PropertyType.values()));
        return Result.success(map);
    }

    public Map<String,JSONObject> getDepartmentMapByName() throws Exception {
        JSONObject result = doveClient.post("/basic/department/service/list", http->{});

        JSONArray data = result.getJSONArray("data");

        if (Objects.isNull(result) || Objects.isNull(data)) {
            return new HashMap<>();
        }

        Map<String,JSONObject> departmentMap = new HashMap<>();
        data.forEach(item->{
            JSONObject obj = (JSONObject) item;
            String name = obj.getString("name");
            String parent_id = obj.getString("parent_id");
            String key = name + "-" + parent_id;
            departmentMap.put(key, obj);
        });

        return departmentMap;
    }

    public Map<String,List<JSONObject>> getDepartmentMapByPid() throws Exception {
        JSONObject result = doveClient.post("/basic/department/service/list", http->{});

        JSONArray data = result.getJSONArray("data");

        if (Objects.isNull(result) || Objects.isNull(data)) {
            return new HashMap<>();
        }

        Map<String,List<JSONObject>> departmentMap = new HashMap<>();
        data.forEach(item->{
            JSONObject obj = (JSONObject) item;
            String parent_id = obj.getString("parent_id");

            List<JSONObject> list = departmentMap.get(parent_id);
            if(Objects.isNull(list)){
                list = new ArrayList<>();
            }
            list.add(obj);

            departmentMap.put(parent_id, list);
        });

        return departmentMap;
    }

    public JSONArray getEmployee(List<String> departments, String name) throws Exception {
        JSONObject result = doveClient.post("/basic/employee/service/list", http->{
            JSONObject body = new JSONObject();
            body.put("department", departments);
            body.put("name", name);

            body.put("pageNo", 1);
            body.put("pageSize", 1000000);

            http.setBody(body.toString());
        });

        JSONArray data = result.getJSONObject("data").getJSONArray("list");

        if (Objects.isNull(data)) {
            return null;
        }

        return data;
    }

    public List<String> getChildDepartments(List<String> departments) throws Exception {
        JSONObject result = doveClient.post("/basic/department/service/getChildDepartments", http->{
            JSONObject body = new JSONObject();
            body.put("pidList", departments);
            http.setBody(body.toString());
        });

        if (result.getIntValue("code") == 200) {
            JSONArray data = result.getJSONArray("data");
            if(Objects.nonEmpty(data)){
                return JSONObject.parseArray(data.toJSONString(), String.class);
            }
        }
        return new ArrayList<>();
    }

    public XSSFWorkbook generateWorkBook() throws Exception {
        // 生成模板文件
        XSSFWorkbook wb = new XSSFWorkbook();

        // 设置头信息
        Sheet sheet = setSheetCellStyle(wb);

        int cellNum = 0;

        Row row = sheet.createRow(3);
        row.createCell(cellNum++).setCellValue("七星商业广场");
        row.createCell(cellNum++).setCellValue("七星广场");
        row.createCell(cellNum++).setCellValue("广东省,深圳市,南山区");
        row.createCell(cellNum++).setCellValue("深南大道以北，华夏街以东");
        row.createCell(cellNum++).setCellValue("10");
        row.createCell(cellNum++).setCellValue("230");
        row.createCell(cellNum++).setCellValue("710");
        row.createCell(cellNum++).setCellValue("50");
        row.createCell(cellNum++).setCellValue("深业沙河（集团）有限公司");
        row.createCell(cellNum++).setCellValue("板塔结合,联排,高层");
        row.createCell(cellNum++).setCellValue("普通住宅毛坯，商住公寓带装修");
        row.createCell(cellNum++).setCellValue("1010.00");
        row.createCell(cellNum++).setCellValue("1020.00");
        row.createCell(cellNum++).setCellValue("12.10%");
        row.createCell(cellNum++).setCellValue("12.10%");
        row.createCell(cellNum++).setCellValue("普通住宅:70年，商住公寓:70年");
        row.createCell(cellNum++).setCellValue("");

        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;

    }

    public XSSFWorkbook generateWorkBook(List<Community> list) throws Exception {
        // 生成模板文件
        XSSFWorkbook wb = new XSSFWorkbook();

        // 设置头信息
        Sheet sheet = setSheetCellStyle(wb);

        Set<String> departmentIds = new HashSet<>();
        Set<String> employeeIds = new HashSet<>();
        for (int i = 0; i < list.size(); i++) {
            Community community = list.get(i);
            departmentIds.addAll(community.getParent_ids());
            employeeIds.addAll(community.getEmployee_ids());
        }

        Map<String,JSONObject> departmentMap = communityService.getDepartmentMap(new ArrayList<>(departmentIds));
        Map<String,JSONObject> employeeMap = communityService.getEmployeeMap(new ArrayList<>(employeeIds));

        int cellNum = 0;
        for (int i = 0; i < list.size(); i++) {
            cellNum = 0;
            Community community = list.get(i);
            Row row = sheet.createRow(i + 3);
            row.createCell(cellNum++).setCellValue(community.getName());
            row.createCell(cellNum++).setCellValue(community.getViewName());
            row.createCell(cellNum++).setCellValue(String.join(",", community.getArea_names()));
            row.createCell(cellNum++).setCellValue(community.getAddress());
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getBuildNum()) ? community.getBuildNum()+ "" : "");
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getFamilyNum()) ? community.getFamilyNum()+ "" : "");
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getParkNum())? community.getParkNum() + "" : "");
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getShopNum()) ? community.getShopNum()+ "" : "");
            row.createCell(cellNum++).setCellValue(community.getDeveloper());
            List<String> buildTypeDescs = new ArrayList<>();
            community.getBuildType().forEach(type->{
                buildTypeDescs.add(EnumUtil.getEnum(BuildType.class, type).getDesc());
            });
            row.createCell(cellNum++).setCellValue(String.join(",", buildTypeDescs));
            row.createCell(cellNum++).setCellValue(community.getDecorationDesc());
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getAreaSize()) ? community.getAreaSize().setScale(2) + "" : "");
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getBuildSize()) ? community.getBuildSize().setScale(2) + "" : "");
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getVolumeRate()) ? community.getVolumeRate().setScale(2) + "%" : "");
            row.createCell(cellNum++).setCellValue(Objects.nonNull(community.getGreeningRate()) ? community.getGreeningRate().setScale(2) + "%" : "");
            row.createCell(cellNum++).setCellValue(community.getPropertyYear());

            List<String> deptNames = new ArrayList<>();
            community.getParent_ids().forEach(id->{
                JSONObject json = departmentMap.get(id);
                if(Objects.nonEmpty(json)) {
                    deptNames.add(json.getString("name"));
                }
            });
            row.createCell(cellNum++).setCellValue(String.join("-", deptNames));

            List<String> empNames = new ArrayList<>();
            community.getEmployee_ids().forEach(id->{
                JSONObject json = employeeMap.get(id);
                if(Objects.nonEmpty(json)){
                  empNames.add(json.getString("name"));
                }
            });
            row.createCell(cellNum++).setCellValue(String.join(",", empNames));
        }

        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    private Sheet setSheetCellStyle(XSSFWorkbook wb){
        //设置字体颜色+文字水平居中
        CellStyle fontColorCellStyle = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        XSSFColor color = new XSSFColor(new java.awt.Color(255,0,0));
        font.setColor(color);
        fontColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
        fontColorCellStyle.setFont(font);

        // 允许文字换行
        XSSFFont fontStyle = wb.createFont(); // 字体样式
//        fontStyle.setBold(true);// 加粗
        fontStyle.setFontName("宋体");
        fontStyle.setFontHeightInPoints((short) 16); // 大小
        CellStyle noticeCellStyle = wb.createCellStyle();
        noticeCellStyle.setWrapText(true);
        noticeCellStyle.setFont(fontStyle);

        // 文字水平居中
        CellStyle fontCellStyle = wb.createCellStyle();
        fontCellStyle.setAlignment(HorizontalAlignment.CENTER);

        Sheet sheet = wb.createSheet();
        // 设置第1行 合并17格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));
        // 设置第2行 合并格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 15));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 17));

        //第1行 须知内容
        Row noticeRow = sheet.createRow(0);
        Cell noticeRowCell = noticeRow.createCell(0);
        noticeRowCell.setCellStyle(noticeCellStyle);
        StringBuffer notice = new StringBuffer();
        notice.append("填写须知：").append("\n")
                .append(" <1>红色字段为必填字段，黑色字段为选填字段；").append("\n")
                .append(" <2>手机号：如果是国内手机号，直接填写手机号即可，如果是国外手机号，需要添加国家码，例如+xx-xxxxxx；").append("\n")
                .append(" <3>建筑类型：多建筑类型用英文的“,”隔开；例如塔楼，高层，超高层；").append("\n")
                .append(" <4>装修状况：多装修状况用英文的“,”隔开；例如普通住宅毛坯，商住公寓带装修；").append("\n")
                .append(" <5>产权年限：多产权年限用英文的“,”隔开；例如普通住宅:70年，商住公寓:70年；").append("\n")
                .append(" <6>所属部门：上下级部门间用‘-’隔开，且从最上级部门开始，例如“万科物业有限公司-深圳事业部-七星商业广场”，多部门用英文的“,”隔开；").append("\n")
                .append(" <7>物业管家：多物业管家用英文的“,”隔开；例如普通住宅:张三，李四，王二；").append("\n");

        noticeRowCell.setCellValue(notice.toString());
        //自动设置行高
        int colLength = noticeRowCell.getStringCellValue().getBytes().length * 6;
        noticeRow.setHeight((short) (colLength));

        //第2行 员工信息：
        Row firstRow = sheet.createRow(1);
        Cell firstRowCell = firstRow.createCell(0);
        firstRowCell.setCellValue("项目/小区信息");
        firstRowCell.setCellStyle(fontCellStyle);
        firstRowCell = firstRow.createCell(8);
        firstRowCell.setCellValue("小区档案");
        firstRowCell.setCellStyle(fontCellStyle);
        firstRowCell = firstRow.createCell(16);
        firstRowCell.setCellValue("物业管理");
        firstRowCell.setCellStyle(fontCellStyle);

        //第3行 表头
        Row headerRow = sheet.createRow(2);
        for (int i = 0; i < EXCEL_IMPORT_HEADER.length; i++) {
            Cell cell = headerRow.createCell(i);
            if(i == 0 || i > 1 && i < 4){
                cell.setCellStyle(fontColorCellStyle);
            }else{
                cell.setCellStyle(fontCellStyle);
            }
            cell.setCellValue(EXCEL_IMPORT_HEADER[i]);
        }

        return sheet;
    }

    private void checkExcelData(Row row, Map<String,JSONObject> departmentMap, Map<String,List<JSONObject>> departmentsMap, JSONObject json) throws Exception {

        BigDecimal buildNum = ExcelUtil.getBigDecimalFromCell(row, 4);
        if (Objects.nonNull(buildNum)) {
            try {
                json.put("buildNum", buildNum.intValue());
            }catch (Exception e){
            }
        }

        BigDecimal familyNum = ExcelUtil.getBigDecimalFromCell(row, 5);
        if (Objects.nonNull(familyNum)) {
            try {
                json.put("familyNum", familyNum.intValue());
            }catch (Exception e){
            }
        }

        BigDecimal parkNum = ExcelUtil.getBigDecimalFromCell(row, 6);
        if (Objects.nonNull(parkNum)) {
            try {
                json.put("parkNum", parkNum.intValue());
            }catch (Exception e){
            }
        }
        BigDecimal shopNum = ExcelUtil.getBigDecimalFromCell(row, 7);
        if (Objects.nonNull(shopNum)) {
            try {
                json.put("shopNum", shopNum.intValue());
            }catch (Exception e){
            }
        }

        String buildTypeDesc = ExcelUtil.getCellValue(row.getCell(9));
        if(Objects.nonEmpty(buildTypeDesc)){
            List<String> buildTypeDescs = Arrays.asList(buildTypeDesc.split(","));
            List<String> buildTypeIds = new ArrayList<>();
            buildTypeDescs.forEach(desc->{
                BuildType buildType = BuildType.get(desc);
                if(Objects.nonNull(buildType)){
                    buildTypeIds.add(buildType.getId());
                }
            });
            if(Objects.nonEmpty(buildTypeIds)){
                json.put("buildType", String.join(",", buildTypeIds));
            }
        }

        String areaSize = ExcelUtil.getCellValue(row.getCell(11));
        if(Objects.nonEmpty(areaSize)){
            try {
                json.put("areaSize", new BigDecimal(areaSize));
            }catch (Exception e){
            }
        }

        String buildSize = ExcelUtil.getCellValue(row.getCell(12));
        if(Objects.nonEmpty(buildSize)){
            try {
                json.put("buildSize",new BigDecimal(buildSize));
            }catch (Exception e){
            }
        }

        String volumeRate = ExcelUtil.getCellValue(row.getCell(13));
        if(Objects.nonEmpty(volumeRate)){
            try {
                if(volumeRate.contains("%")){
                    json.put("volumeRate", new BigDecimal(volumeRate.substring(0,volumeRate.indexOf("%"))));
                }else{
                    json.put("volumeRate", new BigDecimal(volumeRate).multiply(new BigDecimal(100)));
                }
            }catch (Exception e){
            }
        }

        String greeningRate = ExcelUtil.getCellValue(row.getCell(14));
        if(Objects.nonEmpty(greeningRate)){
            try {
                if(greeningRate.contains("%")){
                    json.put("greeningRate", new BigDecimal(greeningRate.substring(0,greeningRate.indexOf("%"))));
                }else{
                    json.put("greeningRate", new BigDecimal(greeningRate).multiply(new BigDecimal(100)));
                }
            }catch (Exception e){
            }
        }

        String deptName = ExcelUtil.getCellValue(row.getCell(16));

        JSONObject tempJson = null;
        if(Objects.nonEmpty(deptName)){
            List<String> deptNames = Arrays.asList(deptName.split("-"));
            String pid = "null";
            for (String item: deptNames) {
                String key = item + "-" + pid;
                JSONObject deptJson = departmentMap.get(key);
                if(Objects.nonNull(deptJson)){
                    pid = deptJson.getString("id");
                    if(deptNames.get(deptNames.size() - 1).equals(item)){
                        tempJson = deptJson;
                        json.put("parent_id", deptJson.getString("id"));
                        JSONArray array = deptJson.getJSONArray("parent_ids");
//                        array.add(deptJson.getString("id"));
                        List<String>parent_ids = JSONObject.parseArray(array.toString() ,String.class);
                        json.put("parent_ids", String.join("," ,parent_ids));
                    }
                }
            }
        }

        String employeeName = ExcelUtil.getCellValue(row.getCell(17));
        if(Objects.nonNull(tempJson)){
            Set<String> deptIds = new HashSet<>();

            //递归获取到所有子级部门
            String pid = tempJson.getString("id");
            deptIds.add(pid);
            getChildIds(deptIds, pid, departmentsMap);

            if(Objects.nonEmpty(employeeName)){
                List<String> employeeNames = Arrays.asList(employeeName.split(","));
                List<String> employeeIds = new ArrayList<>();
                for (String empName: employeeNames) {
                    JSONArray employees = getEmployee(new ArrayList<>(deptIds), empName);
                    if(employees.size() > 0){
                        JSONObject employee = (JSONObject) employees.get(0);
                        employeeIds.add(employee.getString("id"));
                    }
                }
                json.put("employee_ids", String.join(",", employeeIds));
            }
        }
    }

    private void checkExcelData(Row row, int index, List<String> errMsgs, Map<Integer,Integer> errCount, Map<String,JSONObject> departmentMap, Map<String,List<JSONObject>> departmentsMap) throws Exception {

        String name = ExcelUtil.getCellValue(row.getCell(0));
        if (Objects.isEmpty(name)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【项目名称】不能为空");
        }

        String areaName = ExcelUtil.getCellValue(row.getCell(2));
        if (Objects.isEmpty(areaName)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【所在地区】不能为空");
        }else{
            String[] area_names = areaName.split(",");
            if(area_names.length < 3){
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：【所在地区】格式有误");
            }
        }

        String address = ExcelUtil.getCellValue(row.getCell(3));
        if (Objects.isEmpty(address)) {
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【详细地址】不能为空");
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row, 4);
        }catch (Exception e){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【楼栋数】校验出现异常请检查");
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row,5);
        }catch (Exception e){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【总户数】校验出现异常请检查");
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row, 6);
        }catch (Exception e){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【停车位】校验出现异常请检查");
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row, 7);
        }catch (Exception e){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【商铺数】校验出现异常请检查");
        }

        String buildTypeDesc = ExcelUtil.getCellValue(row.getCell(9));
        if(Objects.nonEmpty(buildTypeDesc)){
            List<String> buildTypeDescs = Arrays.asList(buildTypeDesc.split(","));
            List<String> buildTypeIds = new ArrayList<>();
            buildTypeDescs.forEach(desc->{
                BuildType buildType = BuildType.get(desc);
                if(Objects.isNull(buildType)){
                    errCount.put(index, 1);
                    errMsgs.add("第" + index + "行：[" + desc + "]【建筑类别】不匹配");
                }else{
                    buildTypeIds.add(buildType.getId());
                }
            });
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row, 11);
        }catch (Exception e){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【占地面积】校验出现异常请检查");
        }

        try {
            ExcelUtil.getBigDecimalFromCell(row, 12);
        }catch (Exception e){
            errCount.put(index, 1);
            errMsgs.add("第" + index + "行：【建筑面积】校验出现异常请检查");
        }

        String volumeRate = ExcelUtil.getCellValue(row.getCell(13));
        if(Objects.nonEmpty(volumeRate)){
            try {
                if(volumeRate.contains("%")){
                    new BigDecimal(volumeRate.substring(0,volumeRate.indexOf("%")));
                }else{
                   new BigDecimal(volumeRate).multiply(new BigDecimal(100));
                }
            }catch (Exception e){
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：【容积率】校验出现异常请检查");
            }
        }

        String greeningRate = ExcelUtil.getCellValue(row.getCell(14));
        if(Objects.nonEmpty(greeningRate)){
            try {
                if(greeningRate.contains("%")){
                    new BigDecimal(greeningRate.substring(0,greeningRate.indexOf("%")));
                }else{
                    new BigDecimal(greeningRate).multiply(new BigDecimal(100));
                }
            }catch (Exception e){
                errCount.put(index, 1);
                errMsgs.add("第" + index + "行：【绿化率】校验出现异常请检查");
            }
        }

        String deptName = ExcelUtil.getCellValue(row.getCell(16));
        JSONObject tempJson = null;
        if(Objects.nonEmpty(deptName)){
            List<String> deptNames = Arrays.asList(deptName.split("-"));
            String pid = "null";
            for (String item: deptNames) {
                String key = item + "-" + pid;
                JSONObject deptJson = departmentMap.get(key);
                if(Objects.isNull(deptJson)){
                    errCount.put(index, 1);
                    errMsgs.add("第" + index + "行：未获取到【"+ item +"】部门信息");
                }else{
                    pid = deptJson.getString("id");
                }
            }
        }

        String employeeName = ExcelUtil.getCellValue(row.getCell(17));
        if(Objects.nonNull(tempJson)){
            Set<String> deptIds = new HashSet<>();

            //递归获取到所有子级部门
            String pid = tempJson.getString("id");
            deptIds.add(pid);
            getChildIds(deptIds, pid, departmentsMap);

            if(Objects.nonEmpty(employeeName)){
                List<String> employeeNames = Arrays.asList(employeeName.split(","));
                List<String> employeeIds = new ArrayList<>();
                for (String empName: employeeNames) {
                    JSONArray employees = getEmployee(new ArrayList<>(deptIds), empName);
                    if(employees.size() == 0){
                        errCount.put(index, 1);
                        errMsgs.add("第" + index + "行：未获取到员工【"+ empName +"】的信息");
                    }else{
                        JSONObject employee = (JSONObject) employees.get(0);
                        employeeIds.add(employee.getString("id"));
                    }
                }
            }
        }
    }

    private void setExcelData(Community item, JSONObject json) throws Exception {
        item.setName(json.getString("name"));
        item.setViewName(json.getString("viewName"));
        item.setArea_ids(null);
        item.setArea_names(Arrays.asList(json.getString("area_names").split(",")));
        item.setAddress(json.getString("address"));
        item.setDeleted(false);

        item.setBuildNum(json.getInteger("buildNum"));
        item.setFamilyNum(json.getInteger("familyNum"));
        item.setParkNum(json.getInteger("parkNum"));
        item.setShopNum(json.getInteger("shopNum"));
        item.setDeveloper(json.getString("developer"));
        String buildType = json.getString("buildType");
        if(Objects.nonEmpty(buildType)){
            item.setBuildType(Arrays.asList(buildType.split(",")));
        }else{
            item.setBuildType(null);
        }

        item.setDecorationDesc(json.getString("decorationDesc"));
        item.setPropertyYear(json.getString("propertyYear"));
        item.setAreaSize(json.getBigDecimal("areaSize"));
        item.setBuildSize(json.getBigDecimal("buildSize"));
        item.setVolumeRate(json.getBigDecimal("volumeRate"));
        item.setGreeningRate(json.getBigDecimal("greeningRate"));
        item.setParent_id(json.getString("parent_id"));
        String parent_ids = json.getString("parent_ids");
        if(Objects.nonEmpty(parent_ids)){
            item.setParent_ids(Arrays.asList(parent_ids.split(",")));
        }else{
            item.setParent_ids(null);
        }
        String employee_ids = json.getString("employee_ids");
        if(Objects.nonEmpty(employee_ids)){
            item.setEmployee_ids(Arrays.asList(employee_ids.split(",")));
        }else{
            item.setEmployee_ids(null);
        }

        // 更新小区标签信息
        List<String> tags = communityService.saveOrUpdateTag(item);
        if(Objects.nonEmpty(tags)){
            item.setTags(tags);
        }
    }

    private void getChildIds(Set<String> deptIds ,String pid, Map<String,List<JSONObject>> departmentsMap){
        List<JSONObject> list = departmentsMap.get(pid);

        if(Objects.isEmpty(list)){
            return;
        }

        for (JSONObject json: list) {
            pid = json.getString("id");
            deptIds.add(pid);
            getChildIds(deptIds, pid, departmentsMap);
        }
    }

    private void saveAttributeRecord(Token token ,Community com, boolean isSave) throws Exception {
        JSONObject json = new JSONObject();
        json.put("businessId", com.getId());
        json.put("businessType", businessType);
        json.put("currentNo", com.getParent_id());
        json.put("currentNos", com.getParent_ids());
        if(isSave){
            basicService.saveAttributeRecord(token, json);
        } else {
            Community old = Pool.get(Community.class, com.getId());
            if(Objects.isEmpty(com.getParent_id())){
                com.setParent_id("");
            }
            if(Objects.isEmpty(old.getParent_id())){
                old.setParent_id("");
            }

            if(!Objects.equal(old.getParent_id(),com.getParent_id())){
                json.put("beforeNo", old.getParent_id());
                json.put("beforeNos", old.getParent_ids());
                basicService.saveAttributeRecord(token, json);
            }
        }

    }

    @ReqMapping("/getCommunityCascaders")
    public Object getCommunityCascaders(Token token) throws Exception{
        ArrayList<JSONObject> array = new ArrayList<JSONObject>();
        String corpId=token.getCompanyNo();   //企业id
        logger.info("调用getCommunityCascaders,corpId========="+corpId);
        if(corpId!=null){
            JSONObject wyJson=new JSONObject();
            wyJson.put("value",corpId);
            wyJson.put("label",token.getCompanyName());
            wyJson.put("children", getCommunityByCorpId(corpId));
            array.add(wyJson);
        }else{
            List<Corp>corpList=sqlSession.findArray(Corp.class).eq("deleted",false).exe();
            logger.info("调用getCommunityCascaders,corpList.size()========="+corpList.size());
            corpList.forEach(item->{
                JSONObject wyJson=new JSONObject();
                wyJson.put("value",item.getId());
                wyJson.put("label",item.getName());
                wyJson.put("children",getCommunityByCorpId(item.getId()));
                array.add(wyJson);
            });
            ThreadLocalCache.current().setCorpId(null);
        }
        return array;
    }

    private List<JSONObject>getCommunityByCorpId(String corpId){
        logger.info("调用getCommunityByCorpId，corpId="+corpId);
        List array=new ArrayList<JSONObject>();
        try {
            ThreadLocalCache.current().setCorpId(corpId);
            List<Community>communityList=sqlSession.findArray(Community.class).eq("deleted",false).exe();
            for(int i=0;i<communityList.size();i++){
                Community item=communityList.get(i);
                if(i==0){
                    JSONObject jsonObject=new JSONObject();
                    jsonObject.put("value","1");
                    jsonObject.put("label","默认(全部项目)");
                    array.add(jsonObject);
                }
                JSONObject jsonObject=new JSONObject();
                jsonObject.put("value",item.getId());
                jsonObject.put("label",item.getName());
                array.add(jsonObject);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
        }
        return array;
    }
}
